package com.dy.yunying.biz.dao.datacenter.impl;


import com.dy.yunying.api.entity.usergroup.UserGroup;
import com.dy.yunying.api.enums.DimensionEnum;
import com.dy.yunying.api.enums.UserGroupTypeEnum;
import com.dy.yunying.api.req.usergroup.UserGroupDetailReq;
import com.dy.yunying.api.vo.usergroup.UserGroupDetailVo;
import com.dy.yunying.biz.config.YunYingProperties;
import com.dy.yunying.biz.dao.ads.usergroup.UserGroupMapper;
import com.dy.yunying.biz.service.manage.GameService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Component(value = "userGroupDetailDao")
@Slf4j
@RequiredArgsConstructor
public class UserGroupDetailDao {

	@Resource(name = "userGroupSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	private final YunYingProperties yunYingProperties;

	private final UserGroupMapper userGroupMapper;



	public List<UserGroupDetailVo> list(UserGroupDetailReq req) {
		final String indentStr = StringUtils.SPACE;
		StringBuilder sql = new StringBuilder();
		Integer dimension = req.getDimension();
		switch (DimensionEnum.getOneByType(dimension)){
			case ACCOUNT:
				sql = this.getAccountResultSql(req, indentStr);
				break;
			case ROLEID:
				sql = this.getRoleResultSql(req, indentStr);
				break;
			default:
				return new ArrayList<>();
		}

		//排序
		if (StringUtils.isNotBlank(req.getDetailValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append(" ORDER BY\n");
			sql.append("    ").append(req.getDetailValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append(" ORDER BY\n");
			sql.append("    group_data_id DESC\n");
		}

		//分页
		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append(" LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size);
		}
		log.info("用户群组详情数据查询sql: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<UserGroupDetailVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserGroupDetailVo.class));
		return list;
	}


	private StringBuilder getAccountResultSql(UserGroupDetailReq req, String indentStr) {
		StringBuilder sql = new StringBuilder();

		sql.append(indentStr).append(getGroupUserAccountSql(req,indentStr)).append("\n");

		sql.append(indentStr).append("SELECT \n");
		sql.append(indentStr).append("t1.group_data_id id, \n");
		sql.append(indentStr).append("t1.group_id groupId, \n");
		sql.append(indentStr).append("t1.username username, \n");
		//sql.append(indentStr).append("dx_app_id dxAppId, \n");
		sql.append(indentStr).append("t2.app_code appCode,  \n");
		sql.append(indentStr).append("t2.app_main appMain, \n");
		sql.append(indentStr).append("t2.chl_app chlApp, \n");
		sql.append(indentStr).append("t2.chl_sub chlSub, \n");
		sql.append(indentStr).append("t2.chl_main chlMain, \n");
		sql.append(indentStr).append("multiIf(t2.account_reg_time=0, null, toDateTime(t2.account_reg_time/1000)) accountRegTime,  \n");
		sql.append(indentStr).append("t2.dx_os dxOs, \n");
		sql.append(indentStr).append("t2.dx_ip dxIp, \n");
		sql.append(indentStr).append("t2.country, \n");
		sql.append(indentStr).append("t2.province, \n");
		sql.append(indentStr).append("t2.city, \n");
		sql.append(indentStr).append("t2.device_brand deviceBrand,  \n");
		sql.append(indentStr).append("t2.device_model deviceModel, \n");
		sql.append(indentStr).append("multiIf(t2.last_login_time=0, null, toDateTime(t2.last_login_time/1000)) lastLoginTime, \n");
		sql.append(indentStr).append("multiIf(t2.last_pay_time=0, null, toDateTime(t2.last_pay_time/1000)) lastPayTime,  \n");
		sql.append(indentStr).append("t2.pay_num_total payNumTotal, \n");
		sql.append(indentStr).append("t2.pay_fee_total payFeeTotal, \n");
		sql.append(indentStr).append("t2.give_money_total giveMoneyTotal, \n");
		sql.append(indentStr).append("t2.currency_amount_total currencyAmountTotal,  \n");
		sql.append(indentStr).append("t2.pay_fee_total + t2.give_money_total + t2.currency_amount_total payAmountTotal ");
		sql.append(indentStr).append("FROM ( \n");
		sql.append(indentStr).append("SELECT group_data_id,group_id, username \n");
		sql.append(indentStr).append("FROM group_user  \n");
		sql.append(indentStr).append(") t1   \n");
		sql.append(indentStr).append("left join (  \n");
		sql.append(indentStr).append("select *  \n");
		sql.append(indentStr).append("FROM   ").append(yunYingProperties.getVOperationAccountTable()).append(" \n");
		sql.append(indentStr).append("where username  GLOBAL in (  \n");
		sql.append(indentStr).append("SELECT username   \n");
		sql.append(indentStr).append("FROM group_user \n");
		sql.append(indentStr).append("  )  \n");
		sql.append(indentStr).append(") t2 \n");
		sql.append(indentStr).append("on t1.username  = t2.username  \n");
		sql.append(indentStr).append("HAVING 1=1  \n");
		addCondition(req, indentStr, sql);
		return sql;
	}



	private StringBuilder getRoleResultSql(UserGroupDetailReq req, String indentStr) {
		StringBuilder sql = new StringBuilder();
		sql.append(indentStr).append(getGroupUserRoleSql(req,indentStr)).append("\n");
		sql.append(indentStr).append("SELECT \n");
		sql.append(indentStr).append("t1.group_data_id id, \n");
		sql.append(indentStr).append("t1.group_id groupId, \n");
		sql.append(indentStr).append("t1.role_id roleId,  \n");
		sql.append(indentStr).append("t2.role_name roleName,   \n");
		sql.append(indentStr).append("t2.areaid,  \n");
		sql.append(indentStr).append("t2.username, \n");
		//sql.append(indentStr).append("dx_app_id dxAppId, \n");
		sql.append(indentStr).append("t2.app_code appCode,  \n");
		sql.append(indentStr).append("t2.app_main appMain, \n");
		sql.append(indentStr).append("t2.chl_app chlApp, \n");
		sql.append(indentStr).append("t2.chl_sub chlSub, \n");
		sql.append(indentStr).append("t2.chl_main chlMain, \n");
		sql.append(indentStr).append("0 accountRegTime,  \n");
		sql.append(indentStr).append("multiIf(t2.role_reg_time=0, null, toDateTime(t2.role_reg_time/1000)) roleRegTime,  \n");
		sql.append(indentStr).append("t2.dx_os dxOs, \n");
		sql.append(indentStr).append("t2.dx_ip dxIp, \n");
		sql.append(indentStr).append("t2.country, \n");
		sql.append(indentStr).append("t2.province, \n");
		sql.append(indentStr).append("t2.city, \n");
		sql.append(indentStr).append("t2.device_brand deviceBrand,  \n");
		sql.append(indentStr).append("t2.device_model deviceModel, \n");
		sql.append(indentStr).append("multiIf(t2.last_login_time=0, null, toDateTime(t2.last_login_time/1000)) lastLoginTime, \n");
		sql.append(indentStr).append("multiIf(t2.last_pay_time=0, null, toDateTime(t2.last_pay_time/1000)) lastPayTime,  \n");
		sql.append(indentStr).append("t2.pay_num_total payNumTotal, \n");
		sql.append(indentStr).append("t2.pay_fee_total payFeeTotal, \n");
		sql.append(indentStr).append("t2.give_money_total giveMoneyTotal, \n");
		sql.append(indentStr).append("t2.currency_amount_total currencyAmountTotal,  \n");
		sql.append(indentStr).append("t2.pay_fee_total + t2.give_money_total + t2.currency_amount_total payAmountTotal ");
		sql.append(indentStr).append("FROM ( \n");
		switch (UserGroupTypeEnum.getOneByType(req.getType())){
			//规则
			case RULE:
				sql.append(indentStr).append(" SELECT 0 as group_data_id, ").append(" \n");
				break;
			case ID_FILTER:
			default:
				sql.append(indentStr).append(" SELECT group_data_id, ").append(" \n");
				break;
		}
		sql.append(indentStr).append("group_id, role_id, app_main \n");
		sql.append(indentStr).append("FROM group_user  \n");
		sql.append(indentStr).append(") t1   \n");
		sql.append(indentStr).append("left join (  \n");
		sql.append(indentStr).append("select *  \n");
		sql.append(indentStr).append("FROM   ").append(yunYingProperties.getVOperationRoleTable()).append(" \n");
		sql.append(indentStr).append("where (role_id, app_main) GLOBAL in (  \n");
		sql.append(indentStr).append("SELECT role_id, app_main   \n");
		sql.append(indentStr).append("FROM group_user \n");
		sql.append(indentStr).append("  )  \n");
		sql.append(indentStr).append(") t2 \n");
		sql.append(indentStr).append("on t1.role_id = t2.role_id AND t1.app_main = t2.app_main  \n");
		sql.append(indentStr).append("HAVING 1=1  \n");
		addCondition(req, indentStr, sql);
		return sql;
	}

	public Long countDataTotal(UserGroupDetailReq req) {
		final String indentStr = StringUtils.SPACE;
		StringBuilder countSql = new StringBuilder();
		Integer dimension = req.getDimension();
		switch (DimensionEnum.getOneByType(dimension)){
			case ACCOUNT:
				countSql = this.getAccountCountSql(req, indentStr);
				break;
			case ROLEID:
				countSql = this.getRoleCountSql(req, indentStr);
				break;
			default:
				return new Long(0);
		}
		log.info("用户群组详情数据查询sql: [\n{}]", countSql.toString());
		return clickhouseTemplate.queryForObject(countSql.toString(), Long.class);
	}

	private StringBuilder getAccountCountSql(UserGroupDetailReq req, String indentStr) {
		StringBuilder countSql = new StringBuilder();
		countSql.append(indentStr).append(getGroupUserAccountSql(req,indentStr)).append("\n");
		countSql.append(indentStr).append("SELECT \n");
		countSql.append(indentStr).append("count(1) \n");
		countSql.append(indentStr).append("from ( \n");
		countSql.append(indentStr).append("select \n");
		countSql.append(indentStr).append("t1.username username, \n");
		countSql.append(indentStr).append("t2.app_code appCode,   \n");
		countSql.append(indentStr).append("t2.app_main appMain,  \n");
		countSql.append(indentStr).append("t2.chl_app chlApp, \n");
		countSql.append(indentStr).append("t2.chl_sub chlSub, \n");
		countSql.append(indentStr).append("t2.chl_main chlMain \n");
		countSql.append(indentStr).append("FROM ( \n");
		countSql.append(indentStr).append("SELECT group_data_id,group_id, username \n");
		countSql.append(indentStr).append("FROM group_user  \n");
		countSql.append(indentStr).append(") t1   \n");
		countSql.append(indentStr).append("left join (  \n");
		countSql.append(indentStr).append("select  username, app_code, app_main, chl_app, chl_sub, chl_main   \n");
		countSql.append(indentStr).append("FROM   ").append(yunYingProperties.getVOperationAccountTable()).append(" \n");
		countSql.append(indentStr).append("where username  GLOBAL in (  \n");
		countSql.append(indentStr).append("SELECT username   \n");
		countSql.append(indentStr).append("FROM group_user \n");
		countSql.append(indentStr).append("  )  \n");
		countSql.append(indentStr).append(") t2 \n");
		countSql.append(indentStr).append("on t1.username  = t2.username  \n");
		countSql.append(indentStr).append("  ) \n");
		countSql.append(indentStr).append("where 1=1 \n");
		addCondition(req, indentStr, countSql);
		return countSql;
	}


	private StringBuilder getRoleCountSql(UserGroupDetailReq req, String indentStr) {
		StringBuilder countSql = new StringBuilder();
		countSql.append(indentStr).append(getGroupUserRoleSql(req,indentStr)).append("\n");
		countSql.append(indentStr).append("SELECT \n");
		countSql.append(indentStr).append("count(1) \n");
		countSql.append(indentStr).append("from ( \n");
		countSql.append(indentStr).append("select \n");
		countSql.append(indentStr).append("t1.role_id roleId,   \n");
		countSql.append(indentStr).append("t2.areaid,  \n");
		countSql.append(indentStr).append("t2.username, \n");
		countSql.append(indentStr).append("t2.app_code appCode,   \n");
		countSql.append(indentStr).append("t2.app_main appMain,  \n");
		countSql.append(indentStr).append("t2.chl_app chlApp, \n");
		countSql.append(indentStr).append("t2.chl_sub chlSub, \n");
		countSql.append(indentStr).append("t2.chl_main chlMain \n");
		countSql.append(indentStr).append("FROM ( \n");
		switch (UserGroupTypeEnum.getOneByType(req.getType())){
			//规则
			case RULE:
				countSql.append(indentStr).append(" SELECT 0 as group_data_id, ").append(" \n");
				break;
			case ID_FILTER:
			default:
				countSql.append(indentStr).append(" SELECT group_data_id, ").append(" \n");
				break;
		}
		countSql.append(indentStr).append("group_id, role_id, app_main \n");
		countSql.append(indentStr).append("FROM group_user  \n");
		countSql.append(indentStr).append(") t1   \n");
		countSql.append(indentStr).append("left join (  \n");
		countSql.append(indentStr).append("select  role_id, areaid, username, app_code, app_main, chl_app, chl_sub, chl_main   \n");
		countSql.append(indentStr).append("FROM   ").append(yunYingProperties.getVOperationRoleTable()).append(" \n");
		countSql.append(indentStr).append("where (role_id, app_main) GLOBAL in (  \n");
		countSql.append(indentStr).append("SELECT role_id, app_main   \n");
		countSql.append(indentStr).append("FROM group_user \n");
		countSql.append(indentStr).append("  )  \n");
		countSql.append(indentStr).append(") t2 \n");
		countSql.append(indentStr).append("on  t1.role_id = t2.role_id AND t1.app_main = t2.app_main \n");
		countSql.append(indentStr).append("  ) \n");
		countSql.append(indentStr).append("where 1=1 \n");
		addCondition(req, indentStr, countSql);
		return countSql;
	}


	private void addCondition(UserGroupDetailReq req, String indentStr, StringBuilder sql) {
		//账号
		if(StringUtils.isNotBlank(req.getUsername())){
			sql.append(indentStr).append(" AND username =").append("'"+req.getUsername()+"'");
		}

		//分包渠道
		if(StringUtils.isNotBlank(req.getChlApp())){
			sql.append(indentStr).append(" AND chlApp =").append("'"+req.getChlApp()+"'");
		}

		//子渠道
		if(StringUtils.isNotBlank(req.getChlSub())){
			sql.append(indentStr).append(" AND chlSub =").append("'"+req.getChlSub()+"'");
		}
		//主渠道
		if(StringUtils.isNotBlank(req.getChlMain())){
			sql.append(indentStr).append(" AND chlMain =").append("'"+req.getChlMain()+"'");
		}
		//子游戏ID
		if(req.getAppCode() != null){
			sql.append(indentStr).append(" AND appCode =").append(req.getAppCode());
		}

		//主游戏ID
		if(req.getAppMain() != null){
			sql.append(indentStr).append(" AND appMain =").append(req.getAppMain());
		}

		//区服ID
		if(StringUtils.isNotBlank(req.getAreaid())){
			sql.append(indentStr).append(" AND areaid =").append("'"+req.getAreaid()+"'");
		}

		//角色ID
		if(StringUtils.isNotBlank(req.getRoleId())){
			sql.append(indentStr).append(" AND roleId =").append("'"+req.getRoleId()+"'");
		}

	}

	private String getGroupUserAccountSql(UserGroupDetailReq req, String indentStr){
		StringBuilder sql = new StringBuilder();
		switch (UserGroupTypeEnum.getOneByType(req.getType())){
			//规则
			case RULE:
				sql.append(indentStr).append("WITH group_user AS  (").append(" \n");
				sql.append(indentStr).append(" SELECT 0 as group_data_id, group_id, username,  \n");
				sql.append(indentStr).append("  argMax(op_tag, op_version) AS op_tag, max(op_version) AS max_op_version \n");
				sql.append(indentStr).append(" FROM \n");
				sql.append(indentStr).append(yunYingProperties.getDsDyOperationAccount()).append(" \n");
				sql.append(indentStr).append("GROUP BY group_id,username").append(" \n");
				sql.append(indentStr).append(" HAVING op_tag = 1 AND group_id = ").append(req.getGroupId()).append(" \n");
				sql.append(indentStr).append(" )  ").append(" \n");

				break;
			case ID_FILTER:
			default:
				sql.append(indentStr).append("WITH group_user AS  (").append(" \n");
				sql.append(indentStr).append(" SELECT ugd.id AS group_data_id,ugd.group_id, \n");
				sql.append(indentStr).append(" ugd.group_data as username \n");
				sql.append(indentStr).append(" FROM \n");
				sql.append(indentStr).append(yunYingProperties.getDimOperationMysqlUserGroupData()).append("  AS ugd").append(" \n");
				sql.append(indentStr).append("LEFT JOIN  ").append(yunYingProperties.getDimOperationMysqlUserGroup()).append("  AS ug ").append(" \n");
				sql.append(indentStr).append("ON ugd.group_id = ug.id").append(" \n");
				sql.append(indentStr).append(" WHERE ugd.dimension = 1 AND ug.deleted = 0 and ugd.group_id = ").append(req.getGroupId()).append(" \n");
				sql.append(indentStr).append(" )  ").append(" \n");

				break;
		}
		return sql.toString();
	}

	private String getGroupUserRoleSql(UserGroupDetailReq req, String indentStr){
		StringBuilder sql = new StringBuilder();
		switch (UserGroupTypeEnum.getOneByType(req.getType())){
			//规则
			case RULE:
				sql.append(indentStr).append("WITH group_user AS  (").append(" \n");
				sql.append(indentStr).append(" SELECT group_id, role_id, app_main, app_code, areaid, \n");
				sql.append(indentStr).append(" argMax(op_tag, op_version) AS op_tag,max(op_version) AS max_op_version \n");
				sql.append(indentStr).append(" FROM \n");
				sql.append(indentStr).append(yunYingProperties.getDsDyOperationRole()).append(" \n");
				sql.append(indentStr).append("GROUP BY group_id, role_id, app_main, app_code, areaid").append(" \n");
				sql.append(indentStr).append(" HAVING op_tag = 1 AND group_id = ").append(req.getGroupId()).append(" \n");
				sql.append(indentStr).append(" )  ").append(" \n");

				break;
			case ID_FILTER:
			default:
				sql.append(indentStr).append("WITH group_user AS  (").append(" \n");
				sql.append(indentStr).append(" SELECT ugd.id AS group_data_id,ugd.group_id, \n");
				sql.append(indentStr).append(" ugd.group_data as role_id,cast(ug.pgame_id as Int32) as app_main \n");
				sql.append(indentStr).append(" FROM \n");
				sql.append(indentStr).append(yunYingProperties.getDimOperationMysqlUserGroupData()).append("  AS ugd").append(" \n");
				sql.append(indentStr).append("LEFT JOIN  ").append(yunYingProperties.getDimOperationMysqlUserGroup()).append("  AS ug ").append(" \n");
				sql.append(indentStr).append("ON ugd.group_id = ug.id").append(" \n");
				sql.append(indentStr).append(" WHERE ugd.dimension = 2 AND ug.deleted = 0 and ugd.group_id = ").append(req.getGroupId()).append(" \n");
				sql.append(indentStr).append(" )  ").append(" \n");

				break;
		}
		return sql.toString();
	}

}
